package edu.tms.dao;

import java.util.List;
import java.util.Map;

import edu.tms.bean.ClassInfo;
import edu.tms.crud.JdbcUtil;
import edu.tms.dao.common.CommnonDao;
/**
 * 班级信息dao
 * @author Administrator
 *
 */
@SuppressWarnings("rawtypes")
public class ClassInfoDaoImp implements CommnonDao{
	/**
	 * 保存内容
	 * @param value
	 * @return 
	 * @throws Exception
	 */
	@Override
	public long save(Object value) throws Exception {
		String sql="insert into t_class_info value(null,?,?,?,?,?,?);";
		if(value instanceof ClassInfo){
			ClassInfo cla=(ClassInfo)value;
			long num=JdbcUtil.insertAuto(sql,
					cla.getClassName(),
					cla.getMajorName(),
					cla.getClassTchId(),
					cla.getClassSize(),
					cla.getCreateTime(),
					cla.getClassState());
			return num;
		}else return 0;
	}
	/**
	 * 修改内容
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int update(Object value) throws Exception {
		String sql="update t_class_info set class_name=?,major_name=?,"
				+ "class_tch_id=?,class_size=?,create_time=?,class_state=? where class_id=?";
		if(value instanceof ClassInfo){
			ClassInfo cla=(ClassInfo)value;
			Integer num=JdbcUtil.update(sql,
					cla.getClassName(),
					cla.getMajorName(),
					cla.getClassTchId(),
					cla.getClassSize(),
					cla.getClassState(),
					cla.getCreateTime(),
					cla.getClassState(),
					cla.getClassId());
			return num;
		}else return 0;
	}
	/**
	 * 删除内容
	 * 学生表t_student
	 * 作业信息表t_homework，特设展示板块表t_class_activity
	 * 班级财务信息表t_finance
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int delete(Object value) throws Exception {
		String sql="DELETE t_class_info,t_student,t_homework,t_class_activity,t_class_fee,t_squad "
				+ "FROM t_class_info "
				+ "LEFT JOIN t_class_fee ON t_class_info.class_id=t_class_fee.class_id "
				+ "LEFT JOIN t_class_activity ON t_class_activity.class_id=t_class_info.class_id "
				+ "LEFT JOIN t_student ON t_class_info.class_name=t_student.stu_class_name "
				+ "LEFT JOIN t_squad ON t_squad.class_name=t_class_info.class_name "
				+ "LEFT JOIN t_homework ON t_class_info.class_name=t_homework.class_name "
				+ "WHERE t_class_info.class_id=?";
		int num=JdbcUtil.update(sql,value);
		return num;
	}
	/**
	 * 新加用于查询管理辅导员所对应管理的班级
	 * @param key
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<ClassInfo> findCounselor(Integer key) throws Exception {
		String sql="select * from t_class_info where class_tch_id=? ";
		List<ClassInfo> cla=JdbcUtil.query(ClassInfo.class,
				sql, key);
		return cla;
	}
	/**
	 * 查找所有的内容
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<ClassInfo> findAll(int pageNo, int pageSize) throws Exception {
		String sql="select * from t_class_info limit ?,?;";
		List<ClassInfo> cla=JdbcUtil.query(ClassInfo.class, sql,
				(pageNo-1)*pageSize,pageSize);
		return cla;
	}
	
	/**
	 * 查询全部班级的信息
	 * @throws Exception 
	 */
	@SuppressWarnings("unchecked")
	public List<ClassInfo> findClass() throws Exception{
		String sql="select * from t_class_info";
		
		return JdbcUtil.query(ClassInfo.class, sql);
	}
	
	/**
	 * 查询教师所教授的班级信息
	 * @param tchId
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<ClassInfo> findByTch(int tchId, int pageNo, int pageSize) throws Exception{
		String sql = "select * from t_class_info ci,t_class_teacher ct "
				+ "where ci.class_id = ct.class_id and ct.tch_id = ? GROUP BY ci.class_id limit ?,? ";
		return JdbcUtil.query(ClassInfo.class, sql, tchId, (pageNo-1)*pageSize, pageSize);
	}
	
	/**
	 * 根据班级名称查询班级
	 * @param className
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	public ClassInfo findByName(String className) throws Exception{
		String sql = "select * from t_class_info where class_name = ?";
		return (ClassInfo) JdbcUtil.uniqueBean(ClassInfo.class, sql, className);
	}
	
	/**
	 * 获取教师所教授的班级页数
	 * @param tchId
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	public long getPagesByTch(int tchId, int pageSize) throws Exception{
		String sql = "select count(*) from t_class_info ci,t_class_teacher ct "
				+ "where ci.class_id = ct.class_id and ct.tch_id = ?";
		long counts = (long) JdbcUtil.uniqueValue(sql, 1, tchId);
		if(pageSize == 0) return 0;
		return counts%pageSize==0?counts/pageSize:counts/pageSize+1;
	}
	
	
	@SuppressWarnings("unchecked")
	public List<ClassInfo> findAll() throws Exception {
		String sql="select * from t_class_info";
		List<ClassInfo> cla=JdbcUtil.query(ClassInfo.class, sql);
		return cla;
	}
	
	/**
	 * 根据辅导员编号查询班级列表
	 * @param tchId
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<ClassInfo> findBytchId(Integer tchId) throws Exception{
		String sql="select class_name,major_name from t_class_info where class_tch_id=?";
		List<ClassInfo> className=JdbcUtil.query(ClassInfo.class, sql, tchId);
		return className;
	}
	
	/**
	 * 根据id查询
	 * @param Key
	 * @throws Exception
	 */
	@Override
	public ClassInfo findByKey(Object key) throws Exception {
		String sql="select * from t_class_info where class_id=?;";
		ClassInfo cla=(ClassInfo) JdbcUtil.uniqueBean(ClassInfo.class,sql,key);
		return cla;
	}
	/**
	 * String 对应  数据库 列名（comment)
	 * Object 对应相应列名值
	 * 返回的是所查找的类中的数据。方便EXCEL导出
	 * @return
	 */
	@Override
	public List<Map<String,Object>> getMapList() throws Exception {
		
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public List<ClassInfo> findAll(int tchId) throws Exception{
		String sql="select * from t_class_info,t_major "
				+ "where t_class_info.major_name=t_major.major_name and t_class_info.class_tch_id=?";
		return JdbcUtil.query(ClassInfo.class, sql,tchId );
	}
	
	/**
	 * 查询班级人数
	 * @param classId
	 * @return
	 * @throws Exception
	 */
	public Integer getClassSize(Integer classId)throws Exception{
		return (Integer) JdbcUtil.uniqueValue("select class_size from t_class_info where class_id = ?", 1, classId);
	}
	@SuppressWarnings("unchecked")
	@Override
	public List<ClassInfo> findAll(Object key, int pageNo, int pageSize) throws Exception {
		String sql="select * from t_class_info ci, t_class_teacher ct "
				+ "where ci.class_id = ct.class_id "
				+ "and ct.tch_id=? limit ?,?";
		List<ClassInfo> cla=JdbcUtil.query(ClassInfo.class,
				sql,key,(pageNo-1)*pageSize,pageSize);
		return cla;
	}
	/**
	 * 邹磊
	 * 查找所有的内容不需要链表查询
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<ClassInfo> findAllInfo(Object key) throws Exception {
		String sql="SELECT * FROM t_class_info WHERE class_tch_id=?";
		List<ClassInfo> cla=JdbcUtil.query(ClassInfo.class, sql,key);
		return cla;
	}
	@SuppressWarnings("unchecked")
	public List<ClassInfo> findAllByStuid(Object key, int pageNo, int pageSize) throws Exception {
		String sql="select * from t_class_info where class_id=? limit ?,?";
		List<ClassInfo> cla=JdbcUtil.query(ClassInfo.class,
				sql, key,key,(pageNo-1)*pageSize,pageSize);
		return cla;
	}
	public long pages() throws Exception{
		String sql="select count(*) from t_class_info";
		long i=(long) JdbcUtil.uniqueValue(sql,1);
		long pages=1;
		if(i%10==0)pages=i/10;
		else pages=i/10+1;
		return pages;
	}
}
